import pandas as pd
import warnings
import os
from PIL import ImageGrab
import pymysql
from com.atzl.databaseconnection.MyDataToData import ToDataBase
from com.atzl.functions.customerservice.sql.AnalyzeData import AnalyzeData
from com.atzl.common.FieldName import FieldName
from com.atzl.common.Paths import ExportWronglySend
from com.atzl.common.StatusCode import StatusCode
import pythoncom
import xlwings as xw
import string
import unicodedata
from wxauto import *
warnings.simplefilter('ignore')

class Datacleansing(ToDataBase, AnalyzeData):

    def how_get_data(self, start_date, end_date, floor, field_name, sorting, who):
        print("判断需求..")
        try:
            if floor == "全部" or field_name == "全部":
                self.get_all_data(start_date, end_date, sorting, who)
            else:
                self.get_data(start_date, end_date, floor, field_name, sorting, who)
            print("完成报表!")
            return StatusCode.SUBTASK_FINISH
        except Exception:
            print("未知错误!")
            return StatusCode.UNKONWN_ERROR


    def sql_to_df(self, start_date, end_date, floor, field_name, sorting):
        print(f"获取{field_name}数据..")
        conn = self.creat_conn()
        cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
        if field_name == "重量段":
            if floor == "所有":
                sql = self.everyday_weightfor_wronglysend(start_date,
                                                          end_date,
                                                          field_name,
                                                          sorting)

            else:
                sql = self.everyday_weightfor_floor_wronglysend(start_date,
                                                                end_date,
                                                                floor,
                                                                field_name,
                                                                sorting)

        else:
            if floor == "所有":
                sql = self.optional_date_field_name_sorting_wronglysend(start_date,
                                                                        end_date,
                                                                        field_name,
                                                                        sorting)
            else:
                sql = self.optional_date_floor_field_name_sorting_wronglysend(start_date,
                                                                              end_date,
                                                                              floor,
                                                                              field_name,
                                                                              sorting)
        cur.execute(sql)  # 执行sql
        data = cur.fetchall()
        df = pd.DataFrame(data)
        cur.close()
        conn.close()
        return df

    # 获取整包错发包号
    def get_packageid_errorpackage(self, start_date, end_date):
        print("获取整包错发包号..")
        conn = self.creat_conn()
        cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = self.query_packageid_allpackageerror(start_date, end_date)
        cur.execute(sql)  # 执行sql
        data = cur.fetchall()
        df = pd.DataFrame(data)
        cur.close()
        conn.close()
        return df

    # 获取整包错发单号
    def get_wayid_errorpackage(self, start_date, end_date):
        print("获取整包错发单号..")
        conn = self.creat_conn()
        cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = self.query_wayid_allpackageerror(start_date, end_date)
        cur.execute(sql)  # 执行sql
        data = cur.fetchall()
        df = pd.DataFrame(data)
        cur.close()
        conn.close()
        return df

    # 获取飘格情况
    def get_dispense_error(self, start_date, end_date):
        print("获取飘格情况..")
        conn = self.creat_conn()
        cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = self.dispense_error_case(start_date, end_date)
        cur.execute(sql)  # 执行sql
        data = cur.fetchall()
        df = pd.DataFrame(data)
        cur.close()
        conn.close()
        return df

    # 获取飘格情况汇总
    def get_dispense_error_sum(self, start_date, end_date):
        print("获取飘格情况汇总..")
        conn = self.creat_conn()
        cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = self.sum_error_case(start_date, end_date)
        cur.execute(sql)  # 执行sql
        data = cur.fetchall()
        df = pd.DataFrame(data)
        cur.close()
        conn.close()
        return df

    def date_format(self, date):
        return self.replace_char(date[6:10], "月", 1) + "日"


    def get_data(self, start_date, end_date, floor, field_name, sorting, who):
        print("获取指定需求数据..")
        the_start_date = self.date_format(start_date)
        the_end_date = self.date_format(end_date)
        title = f"{the_start_date}至{the_end_date}{field_name}{floor}层错发"
        self.Creating_file_Directory(title)
        df = self.sql_to_df(start_date, end_date, floor, field_name, sorting)
        path = f"/{ExportWronglySend.WRONGLYSEND_TABLE}/{title}/{title}.xlsx"
        self.set_style_table(df, title, path, the_start_date, the_end_date, who)
        print("完成报表!")




    def get_all_data(self, start_date, end_date, sorting, who):
        print("获取所有数据..")


        the_start_date = self.date_format(start_date)
        the_end_date = self.date_format(end_date)
        title = f"{the_start_date}至{the_end_date}错发分析"
        self.Creating_file_Directory(title)
        # 所有层
            # 分拣口
        df_title_all_floor_sortingmouth = self.sql_to_df(start_date,
                                                         end_date,
                                                         "所有",
                                                         FieldName.SORTING_MOUTH,
                                                         sorting)
        title_all_floor_sortingmouth = f"{the_start_date}至{the_end_date}{FieldName.SORTING_MOUTH}所有层错发"
            # 设备层数
        df_title_all_floor_floor = self.sql_to_df(start_date,
                                                  end_date,
                                                  "所有",
                                                  FieldName.EQUIPMENT_FLOOR,
                                                  sorting)
        title_all_floor_floor = f"{the_start_date}至{the_end_date}{FieldName.EQUIPMENT_FLOOR}所有层错发"

            # 供包台
        df_title_all_floor_forpackage = self.sql_to_df(start_date,
                                                       end_date,
                                                       "所有",
                                                       FieldName.FOR_PACKAGE_UNITS,
                                                       sorting)
        title_all_floor_forpackage = f"{the_start_date}至{the_end_date}{FieldName.FOR_PACKAGE_UNITS}所有层错发"
            # 建包人员
        df_title_all_floor_person = self.sql_to_df(start_date,
                                                   end_date,
                                                   "所有",
                                                   FieldName.CREATE_PACKAGE_PERSON,
                                                   sorting)
        title_all_floor_person = f"{the_start_date}至{the_end_date}{FieldName.CREATE_PACKAGE_PERSON}所有层错发"
            # 重量段
        df_title_all_floor_weight = self.sql_to_df(start_date,
                                                   end_date,
                                                   "所有",
                                                   FieldName.WEIGHT_GRADING,
                                                   sorting)
        title_all_floor_weight = f"{the_start_date}至{the_end_date}{FieldName.WEIGHT_GRADING}所有层错发"

        df_all_floor = [df_title_all_floor_sortingmouth,
                        df_title_all_floor_floor,
                        df_title_all_floor_forpackage,
                        df_title_all_floor_person,
                        df_title_all_floor_weight]
        title_all_floor = [title_all_floor_sortingmouth,
                           title_all_floor_floor,
                           title_all_floor_forpackage,
                           title_all_floor_person,
                           title_all_floor_weight]


        # 一层
        # 小车
        df_title_one_floor_car = self.sql_to_df(start_date,
                                                end_date, 1,
                                                FieldName.CAR,
                                                sorting)
        title_one_floor_car = f"{the_start_date}至{the_end_date}{FieldName.CAR}一层错发"

        # 分拣口
        df_title_one_floor_sortingmouth = self.sql_to_df(start_date,
                                                         end_date, 1,
                                                         FieldName.SORTING_MOUTH,
                                                         sorting)
        title_one_floor_sortingmouth = f"{the_start_date}至{the_end_date}{FieldName.SORTING_MOUTH}一层错发"

        # 供包台
        df_title_one_floor_forpackage = self.sql_to_df(start_date,
                                                       end_date, 1,
                                                       FieldName.FOR_PACKAGE_UNITS,
                                                       sorting)
        title_one_floor_forpackage = f"{the_start_date}至{the_end_date}{FieldName.FOR_PACKAGE_UNITS}一层错发"
        # 建包人员
        df_title_one_floor_person = self.sql_to_df(start_date,
                                                   end_date, 1,
                                                   FieldName.CREATE_PACKAGE_PERSON,
                                                   sorting)
        title_one_floor_person = f"{the_start_date}至{the_end_date}{FieldName.CREATE_PACKAGE_PERSON}一层错发"
        # 重量段
        df_title_one_floor_weight = self.sql_to_df(start_date,
                                                   end_date, 1,
                                                   FieldName.WEIGHT_GRADING,
                                                   sorting)
        title_one_floor_weight = f"{the_start_date}至{the_end_date}{FieldName.WEIGHT_GRADING}一层错发"

        df_one_floor = [df_title_one_floor_car,
                        df_title_one_floor_sortingmouth,
                        df_title_one_floor_forpackage,
                        df_title_one_floor_person,
                        df_title_one_floor_weight]
        title_one_floor = [title_one_floor_car,
                           title_one_floor_sortingmouth,
                           title_one_floor_forpackage,
                           title_one_floor_person,
                           title_one_floor_weight]
        # 二层
        # 小车
        df_title_two_floor_car = self.sql_to_df(start_date,
                                                end_date, 2,
                                                FieldName.CAR,
                                                sorting)
        title_two_floor_car = f"{the_start_date}至{the_end_date}{FieldName.CAR}二层错发"
        # 分拣口
        df_title_two_floor_sortingmouth = self.sql_to_df(start_date,
                                                         end_date, 2,
                                                         FieldName.SORTING_MOUTH,
                                                         sorting)
        title_two_floor_sortingmouth = f"{the_start_date}至{the_end_date}{FieldName.SORTING_MOUTH}二层错发"

        # 供包台
        df_title_two_floor_forpackage = self.sql_to_df(start_date,
                                                       end_date, 2,
                                                       FieldName.FOR_PACKAGE_UNITS,
                                                       sorting)
        title_two_floor_forpackage = f"{the_start_date}至{the_end_date}{FieldName.FOR_PACKAGE_UNITS}二层错发"

        # 建包人员
        df_title_two_floor_person = self.sql_to_df(start_date,
                                                   end_date, 2,
                                                   FieldName.CREATE_PACKAGE_PERSON,
                                                   sorting)
        title_two_floor_person = f"{the_start_date}至{the_end_date}{FieldName.CREATE_PACKAGE_PERSON}二层错发"

        # 重量段
        df_title_two_floor_weight = self.sql_to_df(start_date,
                                                   end_date, 2,
                                                   FieldName.WEIGHT_GRADING,
                                                   sorting)
        title_two_floor_weight = f"{the_start_date}至{the_end_date}{FieldName.WEIGHT_GRADING}二层错发"

        df_two_floor = [df_title_two_floor_car,
                        df_title_two_floor_sortingmouth,
                        df_title_two_floor_forpackage,
                        df_title_two_floor_person,
                        df_title_two_floor_weight]
        title_two_floor = [title_two_floor_car,
                           title_two_floor_sortingmouth,
                           title_two_floor_forpackage,
                           title_two_floor_person,
                           title_two_floor_weight]
        # 飘格情况分析
        df_dispense_error = self.get_dispense_error(start_date, end_date)
        title_dispense_error = f"{the_start_date}至{the_end_date}分拣口飘格情况"
        df_dispense_error_sum = self.get_dispense_error_sum(start_date, end_date)
        title_dispense_error_sum = f"{the_start_date}至{the_end_date}飘格情况汇总"

        df_dispense_error_list = [df_dispense_error, df_dispense_error_sum]
        title_dispense_error_list = [title_dispense_error, title_dispense_error_sum]

        # 整包错发
        # 包号
        df_allpackage_packageid_error = self.get_packageid_errorpackage(start_date, end_date)
        title_allpackage_packageid_error = f"{the_start_date}至{the_end_date}疑似整包错发包号"
        df_allpackage_wayid_error = self.get_wayid_errorpackage(start_date, end_date)
        title_allpackage_wayid_error = f"{the_start_date}至{the_end_date}相关单号"


        df_allpackage_error = [df_allpackage_packageid_error, df_allpackage_wayid_error]
        title_allpackage_error = [title_allpackage_packageid_error, title_allpackage_wayid_error]

        df_list = [df_all_floor, df_one_floor, df_two_floor, df_dispense_error_list]
        title_list = [title_all_floor, title_one_floor, title_two_floor, title_dispense_error_list]

        if df_allpackage_packageid_error.shape[0] != 0:
            df_list.append(df_allpackage_error)
            title_list.append(title_allpackage_error)


        path = f"/{ExportWronglySend.WRONGLYSEND_TABLE}/{title}/{title}.xlsx"
        self.set_style_all_table(df_list, title_list, path, the_start_date, the_end_date, who)
        if who != "":
            self.to_wx_msg(who, path)



    def set_style_all_table(self, data, title, excel_file, start_date, end_date, who):
        print("设置报表样式..")
        pythoncom.CoInitialize()
        app = xw.App()
        wb = app.books.add()
        wb.sheets[0]
        sheet_names = ['所有层', '一层', '二层', '飘格情况', '整包错发']
        ws = []
        for _ in data:
            ws.append(None)
        for i, j, k, l in zip(data, title, sheet_names, ws):
            l = wb.sheets.add(k)
            start_col = 0
            for p, o in zip(i, j):
                nums_col = p.shape[1]
                nums_row = p.shape[0]
                self.create_style_table(l, start_col, nums_col, nums_row, p, o, start_date, end_date, who)
                start_col += (nums_col + 1)
        wb.save(excel_file)
        wb.close()
        app.quit()

    def set_style_table(self, data, title, excel_file, start_date, end_date, who):
        print("设置报表样式..")
        pythoncom.CoInitialize()
        app = xw.App()
        wb = app.books.add()
        nums_col = data.shape[1]
        nums_row = data.shape[0]
        ws = wb.sheets[0]
        self.create_style_table(ws, 0, nums_col, nums_row, data, title, start_date, end_date, who)
        wb.save(excel_file)
        wb.close()
        app.quit()


    def create_style_table(self, ws, start_col, all_col, all_row, data, title, start_date, end_date, who):
        start_column = self.getColumnName(start_col + 1)
        end_column = self.getColumnName(all_col + start_col)
        cell1_start_column1 = f'{start_column}1'
        cell1_start_column1_end_column1 = f'{start_column}1:{end_column}1'
        ws.range(cell1_start_column1).value = title
        ws.range(cell1_start_column1).api.Font.Size = 18
        ws.range(cell1_start_column1).api.Font.Bold = True
        ws.range(cell1_start_column1_end_column1).api.Merge()
        ws.range(cell1_start_column1_end_column1).api.HorizontalAlignment = -4108  # -4108 水平居中。 -4131 靠左，-4152 靠右。
        ws.range(cell1_start_column1_end_column1).api.VerticalAlignment = -4108  # -4108 垂直居中（默认）。 -4160 靠上，-4107 靠下， -4130 自动换行对齐。
        ws.range(cell1_start_column1_end_column1).color = 248, 242, 0
        ws.range(cell1_start_column1_end_column1).api.Borders(8).LineStyle = 1  # 上边框
        ws.range(cell1_start_column1_end_column1).api.Borders(9).LineStyle = 1  # 下边框
        ws.range(cell1_start_column1_end_column1).api.Borders(7).LineStyle = 1  # 左边框
        ws.range(cell1_start_column1_end_column1).api.Borders(10).LineStyle = 1  # 右边框
        ws.range(cell1_start_column1_end_column1).api.Borders(12).LineStyle = 1  # 内横边框
        ws.range(cell1_start_column1_end_column1).api.Borders(11).LineStyle = 1  # 内纵边框
        ws.range(cell1_start_column1_end_column1).column_width = 35 if title.find("整包错发") > 0 else 15

        for i in range(all_col):
            this_column = self.getColumnName(i + 1 + start_col)
            cell1_this_column2 = f'{this_column}2'
            ws.range(cell1_this_column2).value = data.columns[i]
            ws.range(cell1_this_column2).api.Font.Bold = True
            ws.range(cell1_this_column2).api.HorizontalAlignment = -4152  # -4108 水平居中。 -4131 靠左，-4152 靠右。
            ws.range(cell1_this_column2).api.VerticalAlignment = -4108  # -4108 垂直居中（默认）。 -4160 靠上，-4107 靠下， -4130 自动换行对齐。
            ws.range(cell1_this_column2).color = 192, 192, 192
            ws.range(cell1_this_column2).api.Borders(8).LineStyle = 1  # 上边框
            ws.range(cell1_this_column2).api.Borders(9).LineStyle = 1  # 下边框
            ws.range(cell1_this_column2).api.Borders(7).LineStyle = 1  # 左边框
            ws.range(cell1_this_column2).api.Borders(10).LineStyle = 1  # 右边框
            ws.range(cell1_this_column2).api.Borders(12).LineStyle = 1  # 内横边框
            ws.range(cell1_this_column2).api.Borders(11).LineStyle = 1  # 内纵边框

            for j in range(all_row):
                cell1_this_columnj = f'{this_column}{j + 3}'
                format_value = self.set_format(data.columns[i])
                ws.range(cell1_this_columnj).api.NumberFormat = format_value
                ws.range(cell1_this_columnj).value = f"{self.text_format(data.iloc[j, i], format_value)}"
                ws.range(cell1_this_columnj).api.HorizontalAlignment = -4152  # -4108 水平居中。 -4131 靠左，-4152 靠右。
                ws.range(cell1_this_columnj).api.VerticalAlignment = -4108  # -4108 垂直居中（默认）。 -4160 靠上，-4107 靠下， -4130 自动换行对齐。
                ws.range(cell1_this_columnj).color = 255, 255, 255
                ws.range(cell1_this_columnj).api.Borders(8).LineStyle = 1  # 上边框
                ws.range(cell1_this_columnj).api.Borders(9).LineStyle = 1  # 下边框
                ws.range(cell1_this_columnj).api.Borders(7).LineStyle = 1  # 左边框
                ws.range(cell1_this_columnj).api.Borders(10).LineStyle = 1  # 右边框
                ws.range(cell1_this_columnj).api.Borders(12).LineStyle = 1  # 内横边框
                ws.range(cell1_this_columnj).api.Borders(11).LineStyle = 1  # 内纵边框
        self.copy_save_img(ws, start_column, end_column, all_row, title, start_date, end_date, who)


    def copy_save_img(self, ws, start_column, end_column, all_row, title, start_date, end_date, who):
        all_range = f"{start_column}1:{end_column}{all_row + 2}"
        print("复制图片区域..")
        ws.range(all_range).api.CopyPicture()
        # 粘贴
        ws.api.Paste()

        pic = ws.pictures[0]  # 当前图片
        pic.api.Copy()  # 复制图片
        print("获取图片数据..")
        img = ImageGrab.grabclipboard()  # 获取剪贴板的图片数据
        path = f"/{ExportWronglySend.WRONGLYSEND_TABLE}/{start_date}至{end_date}错发分析/{title}.png"
        img.save(path)  # 保存图片
        # 发送微信
        if who != "":
            self.to_wx_msg(who, path)
        print("成功保存图片..")
        pic.delete()  # 删除sheet上的图片




    def to_wx_msg(self, who, msg):
        try:
            print("对接微信..")
            wx = WeChat()
            print("搜索联系人..")
            wx.ChatWith(who)
            print("发送文件..")
            wx.SendFiles(msg)
        except Exception:
            print("wxauto发生错误!")
            pass
    def set_format(self, text):
        if str(text).find("号") >= 0:
            return "@"
        if str(text).find("万分比") >= 0:
            return "0.00"
        if str(text).find("日期") >= 0:
            return "yyyy年mm月dd日"

    def is_number(self, nums):
        try:
            float(nums)
            return True
        except ValueError:
            pass
        try:
            unicodedata.numeric(nums)
            return True
        except (TypeError, ValueError):
            pass
        return False

    def text_format(self, value, format):
        if self.is_number(value) ^ True:
            return value
        if format == "0.00":
            return value
        if int(value) < 10:
            return f"00{int(value)}"
        elif 10 <= int(value) < 100:
            return f"0{int(value)}"
        elif int(value) >= 100:
            return f"{int(value)}"
    # 单元格列根据索引转换字母
    def getColumnName(self, columnIndex):
        ret = ''
        ci = columnIndex - 1
        index = ci // 26
        if index > 0:
            ret += self.getColumnName(index)
        ret += string.ascii_uppercase[ci % 26]
        return ret

    # 字符串按索引位置替换字符
    def replace_char(self, old_string, char, index):
        old_string = str(old_string)
        # 新的字符串 = 老字符串[:要替换的索引位置] + 替换成的目标字符 + 老字符串[要替换的索引位置+1:]
        new_string = old_string[:index] + char + old_string[index + 1:]
        return new_string

    # 创建文件目录,如有同名目录不创建,没有同名目录就创建目录
    def Creating_file_Directory(self, title):
        print("校验文件目录..")
        try:
            path = f"/{ExportWronglySend.WRONGLYSEND_TABLE}/{title}"
            if not os.path.exists(path):
                os.makedirs(path)
            return True
        except Exception:
            return False


    # 打开文件目录
    def open_files(self):
        try:
            path = f"\\{ExportWronglySend.WRONGLYSEND_TABLE}"
            os.startfile(path)


            return True
        except Exception:
            return False